<?php
	class Subjects extends CI_Model{

		function __construct(){
			parent::__construct();
		}

		function get_subject_list_all($school_id){
			$sql = "SELECT s.* FROM {$this->db->dbprefix}b_subject AS s where s.school_id = {$school_id}";
			return $this->db->query($sql)->result_array();
		}

		function get_subject_list($limit = 0, $offset = 0, $conditions = array()){

			$where = "1 = 1";
			if($conditions){
				if(!empty($conditions['title'])){
					$where .= " AND s.title LIKE '%{$conditions['title']}%'";
				}

				if(!empty($conditions['tk_category_id'])){
					$where .= " AND s.tk_category_id = '{$conditions['tk_category_id']}'";
				}

				if(isset($conditions['school_id'])){
					$where .= " AND s.school_id = '{$conditions['school_id']}'";
				}

				if(!empty($conditions['pack_status'])){
					if($conditions['pack_status'] == 1){
						$where .= " AND tp.id > 0";
						if(!empty($conditions['school_id'])){
							$where .= " AND s.school_id = '{$conditions['school_id']}' AND c.school_id = '{$conditions['school_id']}'";
						}
					}elseif($conditions['pack_status'] == 2){
						$where .= " AND tp.id is null";
						if(!empty($conditions['school_id'])){
							$where .= " AND (s.school_id = '{$conditions['school_id']}' OR s.school_id = 0)";
						}
					}else{
						if(!empty($conditions['school_id'])){
							$where .= " AND (s.school_id = '{$conditions['school_id']}' OR s.school_id = 0)";
						}
					}
				}
			}
			
			$sql = "SELECT SQL_CALC_FOUND_ROWS s.*, tp.id AS pack_id, tp.status, tp.price, tp.on_time, tp.expire_day, tp.commission, c.name FROM {$this->db->dbprefix}b_subject AS s ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}tk_categorys AS c ON(c.id=s.tk_category_id) ";
			$sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school_tk_packages AS tp ON(tp.school_id = '{$this->user_data['school_id']}' AND tp.subject_id=s.id) ";
			$sql .= "WHERE $where ORDER BY s.id DESC ";
			if($limit > 0 || $offset > 0){
			   $sql .= "LIMIT $limit, $offset";
			}
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		/**
		 * [get_chapter_list description]
		 * @param  integer $limit      [description]
		 * @param  integer $offset     [description]
		 * @param  array   $conditions [description]
		 * @return [type]              [description]
		 */
		function get_chapter_list($limit = 0, $offset = 0, $conditions = array()){
			$where = "1 = 1";
			if($conditions){
				if(!empty($conditions['title'])){
					$where .= " AND c.title LIKE '%{$conditions['title']}%'";
				}

				if(!empty($conditions['subject_id'])){
					$where .= " AND c.subject_id = '{$conditions['subject_id']}'";
				}

				if(!empty($conditions['parent_id'])){
					$where .= " AND c.parent_id = '{$conditions['parent_id']}'";
				}

				if(!empty($conditions['school_id'])){
					$where .= " AND s.school_id = '{$conditions['school_id']}'";
				}

				if(!empty($conditions['subject_id'])){
                    $where .= " AND c.subject_id = '{$conditions['subject_id']}'";
                }
			}
			$sql = "SELECT SQL_CALC_FOUND_ROWS c.*, s.title AS sub_title,sc.title as top_title FROM {$this->db->dbprefix}b_subject_chapter AS c ";
			$sql .= " LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=c.subject_id) ";
			$sql .= " LEFT JOIN {$this->db->dbprefix}b_subject_chapter AS sc ON(sc.id=c.parent_id) ";
			$sql .= " WHERE $where ORDER BY c.sort ASC ";
			if($limit > 0 || $offset > 0){
			   $sql .= "LIMIT $limit, $offset";
			}
			$result = $this->db->query($sql)->result_array();
			$row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
			return array("count" => $row_num['row_num'], "result" => $result);
		}

		function get_school_subject_category(){
			$sql = "SELECT * FROM {$this->db->dbprefix}tk_categorys WHERE school_id='{$this->user_data['school_id']}'";
			return $this->db->query($sql)->result_array();
		}
	}